﻿CREATE VIEW dbo.vistaLotesFaenamientosConReclamos
AS
SELECT     dbo.LOTESFAENAMIENTO.IDLOTEFAENAMIENTO, dbo.LOTESFAENAMIENTO.FECHA AS fechaDelLote, dbo.PRODUCTOS.NOMBRE AS NombreProducto, 
                      dbo.PRODUCTOS.CODIGO AS CodigoProducto, dbo.PRODUCTOS.DESCRIPCION AS DescripcionProducto, dbo.ESPECIES.NOMBRE AS NombreEspecie, 
                      COUNT(dbo.RECLAMOS.IDRECLAMO) AS CantidadReclamos, dbo.LOTESFAENAMIENTO.CODIGO AS CodigoFaenamiento
FROM         dbo.ESPECIES INNER JOIN
                      dbo.PRODUCTOS ON dbo.ESPECIES.IDESPECIE = dbo.PRODUCTOS.IDESPECIE RIGHT OUTER JOIN
                      dbo.LOTESFAENAMIENTO INNER JOIN
                      dbo.RECLAMOS ON dbo.LOTESFAENAMIENTO.IDLOTEFAENAMIENTO = dbo.RECLAMOS.idLoteFaenamiento ON 
                      dbo.PRODUCTOS.IDPRODUCTO = dbo.LOTESFAENAMIENTO.IDPRODUCTO
GROUP BY dbo.LOTESFAENAMIENTO.IDLOTEFAENAMIENTO, dbo.LOTESFAENAMIENTO.FECHA, dbo.PRODUCTOS.NOMBRE, dbo.PRODUCTOS.CODIGO, 
                      dbo.PRODUCTOS.DESCRIPCION, dbo.ESPECIES.NOMBRE, dbo.LOTESFAENAMIENTO.CODIGO, dbo.RECLAMOS.baja
HAVING      (dbo.RECLAMOS.baja IS NULL)


GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = -173
         Left = 0
      End
      Begin Tables = 
         Begin Table = "ESPECIES"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 116
               Right = 244
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PRODUCTOS"
            Begin Extent = 
               Top = 42
               Left = 499
               Bottom = 152
               Right = 705
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "LOTESFAENAMIENTO"
            Begin Extent = 
               Top = 70
               Left = 823
               Bottom = 259
               Right = 1029
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "RECLAMOS"
            Begin Extent = 
               Top = 348
               Left = 38
               Bottom = 458
               Right = 258
            End
            DisplayFlags = 280
            TopColumn = 5
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 12
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 13', 'USER', N'dbo', 'VIEW', N'vistaLotesFaenamientosConReclamos', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_DiagramPane2', N'50
      End
   End
End
', 'USER', N'dbo', 'VIEW', N'vistaLotesFaenamientosConReclamos', NULL, NULL

